<?php
/**
 * Description of Emms_Report_WriteOffsByProgram
 *
 * @author andres
 */
class Emms_Report_WriteOffsByProgram extends HTech_Report
{
    protected function _setName() 
    {
        $this->_name = $this->translate('Loan write-offs consolidated by program');
    }

    protected function _setForm() 
    {
        $this->_form = new HTech_Report_Form_DateRange();
    }
    
    protected function _setIndex() {}

    protected function _setSql() 
    {
        $this->_sql = '
            select 
                lwo.WriteOffId,
                lwo.Program,
                count(lwo.WriteOffId) WriteOffs,
                sum(if(cy.Cycle=1,1,0)) FirstTime,
                sum(if(lwo.ClientGender="Female",1,0)) Female,
                sum(if(lwo.ClientGender="Male",1,0)) Male,
                sum(lwo.Amount) Amount,
                sum(lwo.Principal) Principal,
                sum(lwo.LoanInsurance) LoanInsurance,
                sum(lwo.HealthInsurance) HealthInsurance,
                sum(lwo.AdministrativeFee) AdministrativeFee,
                sum(lwo.Interest) Interest,
                sum(lwo.Penalties) Penalties,
                sum(lwo.PrincipalDisbursed) PrincipalDisbursed,
                sum(lwo.PrincipalCharges) PrincipalCharges,
                sum(lwo.PrincipalPayments) PrincipalPayments,
                sum(lwo.PrincipalBalance) PrincipalBalance,
                sum(lwo.LoanInsuranceCharges) LoanInsuranceCharges,
                sum(lwo.LoanInsurancePayments) LoanInsurancePayments,
                sum(lwo.LoanInsuranceBalance) LoanInsuranceBalance,
                sum(lwo.HealthInsuranceCharges) HealthInsuranceCharges,
                sum(lwo.HealthInsurancePayments) HealthInsurancePayments,
                sum(lwo.HealthInsuranceBalance) HealthInsuranceBalance,
                sum(lwo.AdministrativeFeeCharges) AdministrativeFeeCharges,
                sum(lwo.AdministrativeFeePayments) AdministrativeFeePayments,
                sum(lwo.AdministrativeFeeBalance) AdministrativeFeeBalance,
                sum(lwo.InterestBalance) InterestBalance,
                sum(lwo.PenaltiesBalance) PenaltiesBalance,
                sum(if(lwo.Status="Written-Off",1,0)) WrittenOff,
                sum(if(lwo.Status="Recovered",1,0)) Recovered
            from 
                view_loan_write_offs lwo
            left join
                loan_cycles cy on cy.LoanId = lwo.LoanId
            where 
                lwo.WriteOffDate >= :dateFrom and 
                lwo.WriteOffDate <= :dateTo
            group by
                lwo.ProgramId
            order by
                lwo.ProgramId';
    }
    
    protected function _setHeaders()
    {
        $this->_headers = new stdClass();
        $this->_headers->Program = $this->translate('Program');
        $this->_headers->WriteOffs = $this->translate('Write-offs');
        $this->_headers->FirstTime = $this->translate('First cycle');
        $this->_headers->Female = $this->translate('Female');
        $this->_headers->Male = $this->translate('Male');
        $this->_headers->Amount = $this->translate('Writen-off total');
        $this->_headers->Principal = $this->translate('Principal');
        $this->_headers->LoanInsurance = $this->translate('Loan insurance');
        $this->_headers->HealthInsurance = $this->translate('Health insurance');
        $this->_headers->AdministrativeFee = $this->translate('Admin. fee');
        $this->_headers->Interest = $this->translate('Interest');
        $this->_headers->Penalties = $this->translate('Penalties');
        $this->_headers->DeliveryDate = $this->translate('Disbursement date');
        $this->_headers->PrincipalDisbursed = $this->translate('Principal disbursed');
        $this->_headers->PrincipalCharges = $this->translate('Principal charges');
        $this->_headers->PrincipalPayments = $this->translate('Principal payments');
        $this->_headers->PrincipalBalance = $this->translate('Principal balance');
        $this->_headers->LoanInsuranceCharges = $this->translate('Loan insurance charges');
        $this->_headers->LoanInsurancePayments = $this->translate('Loan insurance payments');
        $this->_headers->LoanInsuranceBalance = $this->translate('Loan insurance balance');
        $this->_headers->HealthInsuranceCharges = $this->translate('Health insurance charges');
        $this->_headers->HealthInsurancePayments = $this->translate('Health insurance payments');
        $this->_headers->HealthInsuranceBalance = $this->translate('Health insurance balance');
        $this->_headers->AdministrativeFeeCharges = $this->translate('Admin. fee charges');
        $this->_headers->AdministrativeFeePayments = $this->translate('Admin. fee payments');
        $this->_headers->AdministrativeFeeBalance = $this->translate('Admin. fee balance');
        $this->_headers->InterestBalance = $this->translate('Interest balance');
        $this->_headers->PenaltiesBalance = $this->translate('Penalties balance');
        $this->_headers->WrittenOff = $this->translate('Currently written-offs');
        $this->_headers->Recovered = $this->translate('Currently recovered');
    }
    
    public function setParams($params) 
    {
        $this->_params = array(
            ':dateFrom' => $params['DateFrom'],
            ':dateTo' => $params['DateTo']);
    }

}
